﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.SqlServerCe;
using SqlImEx.Util;

namespace SqlImEx.Sql
{
    public class SqlCeContext : ISqlContext
    {
        SqlCeConnection _conn;
        private List<ForeignKeyConstraint> _fks;
        public bool KeepAlive { get; set; }
        public SqlCeContext(string connectionString)
        {
            _conn = new SqlCeConnection(connectionString);
        }

        public void ExecuteSql(string query)
        {
            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = new SqlCeCommand(query, _conn))
                command.ExecuteNonQuery();
            if (!KeepAlive)
                _conn.Close();
        }
        public DataTable ExecuteSqlQuery(string query)
        {
            DataSet ds;
            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = new SqlCeCommand(query, _conn))
            {
                using (IDataReader rdr = command.ExecuteReader())
                {
                    ds = new DataSet { EnforceConstraints = false };
                    ds.Load(rdr, LoadOption.PreserveChanges, "");
                }
            }
            if (!KeepAlive)
                _conn.Close();
            return ds.Tables[0];
        }

        public TableDefinition GetTableData(string schema, string table)
        {
            var td = new TableDefinition();
            string query = @"SELECT s.name'schema',o.name'table'
FROM SYS.OBJECTS o
inner join sys.schemas s on s.schema_id = o.schema_id
WHERE s.name = @schema and o.name = @table";
            string queryce=  @"select Table_Schema, Table_Name from information_schema.tables where table_name = @table";// and table_Schema = @schema";


            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = new SqlCeCommand(queryce, _conn))
            {
                //if (schema == null)
                //    command.Parameters.AddWithValue("@schema", DBNull.Value);
                //else
                //    command.Parameters.AddWithValue("@schema", schema);
                command.Parameters.AddWithValue("@table", table);
                using (var rdr = command.ExecuteReader(CommandBehavior.SingleResult))
                {
                    rdr.Read();
                    td.name = (string)rdr["Table_Name"];
                    //td.schema = (string)rdr["Table_Schema"];

                }
            }
            GetFieldData(td);
            if (!KeepAlive)
                _conn.Close();
            return td;
        }

        public List<TableDefinition> GetTableData()
        {
            var result = new List<TableDefinition>();
//            string query = @"SELECT s.name'schema',o.name'table'
//FROM SYS.OBJECTS o
//inner join sys.schemas s on s.schema_id = o.schema_id
//WHERE type in ('U') and is_ms_shipped = 0 
//order by s.name,o.name";
            string query = @"select Table_Schema, Table_Name from information_schema.tables";

            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = new SqlCeCommand(query, _conn))
            {
                using (var rdr = command.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        var td = new TableDefinition();
                        td.name = (string)rdr["Table_Name"];
                        td.schema = (rdr["Table_Schema"] == DBNull.Value) ? null : (string)rdr["Table_Schema"];
                        result.Add(td);
                    }
                }
            }
            if (!KeepAlive)
                _conn.Close();
            return result;
        }

        public void SetIdentity(string schema, string table, bool on)
        {
            string formattedTable = "[" + table + "]";
            if (!string.IsNullOrEmpty(schema))
            {
                formattedTable = "[" + schema + "]." + formattedTable;
            }
            string query = string.Format("SET IDENTITY_INSERT {0} {1}", formattedTable, on ? "ON" : "OFF");
            ExecuteSql(query);
        }

        public void DeleteAllFrom(TableDefinition tbl)
        {
            string delstr = "Delete from " + tbl.FullyQualifiedSqlName;
            ExecuteSql(delstr);
        }

        public List<ForeignKeyConstraint> GetForiegnKeyConstraints()
        {
            const string qry = @"SELECT  
     KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
    ,KCU1.TABLE_NAME AS FK_TABLE_NAME
    ,KCU1.CONSTRAINT_SCHEMA as FK_SCHEMA_NAME
    ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
    ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
    ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
    ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME
    ,KCU1.CONSTRAINT_SCHEMA as REFERENCED_SCHEMA_NAME
    ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
    ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 

LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 
    ON --KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
    --AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
     KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 
    ON --KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
    --AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
     KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
    AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION";
            var result = new List<ForeignKeyConstraint>();
            var dt = ExecuteSqlQuery(qry);
            foreach (DataRow dr in dt.Rows)
            {
                var fk = result.FirstOrDefault(x => x.FKConstraintName == (string)dr["FK_CONSTRAINT_NAME"]);
                if (fk == null)
                {
                    result.Add(fk = new ForeignKeyConstraint { FKConstraintName = (string)dr["FK_CONSTRAINT_NAME"] });
                    fk.FKTableName = (string)dr["FK_TABLE_NAME"];
                    fk.FKSchemaName = dr["FK_SCHEMA_NAME"] == DBNull.Value ? null : (string)dr["FK_SCHEMA_NAME"];
                    fk.RefTableName = (string)dr["REFERENCED_TABLE_NAME"];
                    fk.RefSchemaName = dr["REFERENCED_SCHEMA_NAME"] == DBNull.Value ? null : (string)dr["REFERENCED_SCHEMA_NAME"];
                }
                var cols = new ForeignKeyConstraintColumns();
                cols.FKColumnName = (string)dr["FK_COLUMN_NAME"];
                cols.FKOrdinal = (int)dr["FK_ORDINAL_POSITION"];
                cols.RefColumnName = (string)dr["REFERENCED_COLUMN_NAME"];
                cols.RefConstraintName = (string)dr["REFERENCED_CONSTRAINT_NAME"];
                cols.RefOrdinal = (int)dr["REFERENCED_ORDINAL_POSITION"];
                fk._Columns.Add(cols);
            }
            return result;
        }

        public void SetConstraints(bool enable)
        {
            if (!enable)
                _fks = GetForiegnKeyConstraints();
            foreach (var td in GetTableData())
            {
                if (!enable)
                {
                    foreach (var fk in _fks.Where(x => x.RefSchemaName == td.schema && x.RefTableName == td.name))  // ensure this Where() is the same as the one in the else
                        ExecuteSql(string.Format("ALTER TABLE [{0}] DROP CONSTRAINT [{1}]", fk.FKTableName, fk.FKConstraintName));
                }
                else
                {
                    foreach (var fk in _fks.Where(x => x.RefSchemaName == td.schema && x.RefTableName == td.name))
                    {
                        string qry2 = @"ALTER TABLE [{0}] ADD  CONSTRAINT [{1}] FOREIGN KEY({2}) REFERENCES [{3}] ({4})";
                        string qry3 = string.Format(qry2, fk.FKTableName, fk.FKConstraintName, fk._Columns.DelimitedString(c => string.Format("[{0}]", c.FKColumnName), ","), fk.RefTableName, fk._Columns.DelimitedString(c => string.Format("[{0}]", c.RefColumnName), ","));
                        ExecuteSql(qry3);
                    }
                }
            }
        }

        public List<TableSchema> FetchTables()
        {
            DataTable dt = ExecuteSqlQuery(@"select Table_Schema, Table_Name from information_schema.tables");
            List<TableSchema> tss = new List<TableSchema>();
            foreach (DataRow r in dt.Rows)
            {
                tss.Add(new TableSchema
                {
                    Schema = (r["Table_Schema"] == DBNull.Value) ? null : (string)r["Table_Schema"],
                    Table = (string)r["Table_Name"]
                });
            }
            return tss;
        }

        public List<IdentityColumn> GetIdentityColumns()
        {
            List<IdentityColumn> result = new List<IdentityColumn>();
            string query = "select s.name'Schema',t.name'Name', id.name 'ColumnName', id.seed_value, id.increment_value, id.last_value, id.is_not_for_replication\r\nfrom sys.identity_columns id\r\ninner join sys.tables t on t.object_id = id.object_id\r\ninner join sys.schemas s on t.schema_id = s.schema_id\r\norder by t.name";
            string ceQuery = "select Table_name, table_schema, column_name, autoinc_seed, autoinc_next, autoinc_increment from information_schema.columns where autoinc_increment is not null";
            DataTable dt = ExecuteSqlQuery(ceQuery);
            foreach (DataRow r in dt.Rows)
            {
                result.Add(new IdentityColumn
                {
                    Table = (string)r["Table_name"],
                    Schema = r["table_schema"] == DBNull.Value ? null : (string)r["table_schema"],
                    Column = (string)r["column_name"],
                    SeedValue = Convert.ToInt64(r["autoinc_seed"]),
                    IncrementValue = Convert.ToInt64(r["autoinc_increment"]),
                    //NotForReplication = Convert.ToBoolean(r["is_not_for_replication"])
                });
            }
            return result;
        }

        public void GetFieldData(TableDefinition t)
        {
            var result = new List<Field>();

            string cequery = string.Format(@"select Column_Name, Data_Type, Is_Nullable,character_Maximum_length, autoinc_increment
from information_schema.columns
where table_name = '{0}'
order by table_name, ordinal_position", t.name);
            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = new SqlCeCommand(cequery, _conn))
            {
                command.Parameters.AddWithValue("@table", t.name);
                command.Parameters.AddWithValue("@schema", t.schema);
                using (var rdr = command.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        var fd = new Field();
                        fd.name = (string)rdr["Column_Name"];
                        fd.nulls = (string)rdr["is_nullable"] == "YES";
                        fd.datatype = DataTypeConversion.GetType((string)rdr["Data_Type"]);
                        fd.len = rdr["character_Maximum_length"] != DBNull.Value ? Convert.ToInt32(rdr["character_Maximum_length"]) : -1;
                        fd.identity = rdr["autoinc_increment"] != DBNull.Value;
                        result.Add(fd);
                    }
                }
            }
            //foreach (var k in _keys.Where(x => x.TableSchema == t.schema && x.TableName == t.name))
            //    result.First(x => x.name == k.ColumnName).key = true;
            if (!KeepAlive)
                _conn.Close();
            t.Field = result.ToArray();
        }

        public void GetAllRows(TableDefinition t, Action<IDataReader> row)
        {
            string query = "select * from " + t.FullyQualifiedSqlName;
            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = new SqlCeCommand(query, _conn))
            {
                using (var rdr = command.ExecuteReader())
                {
                    while (rdr.Read())
                        row(rdr);
                }
            }
            if (!KeepAlive)
                _conn.Close();
        }
    }
}
